﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Case
{
    public partial class StudentManager : Form
    {
        public List<Student> students = null;
        public StudentManager()
        {
            InitializeComponent();
        }
        private string connectionStr = "server=127.0.0.1;port=3306;user=root;password=root;database=studentcase";//连接数据库
        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }


        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void edit_Click(object sender, EventArgs e)
        {
            
            //dataGridView1.DataSource.students
            Student student= students[dataGridView1.CurrentRow.Index];
            string editStr = $"update student set studentId={dataGridView1["studentId", dataGridView1.CurrentRow.Index].Value},sName='{dataGridView1["sName", dataGridView1.CurrentRow.Index].Value}',sAge={dataGridView1["sAge", dataGridView1.CurrentRow.Index].Value},sLike='{dataGridView1["sLike",dataGridView1.CurrentRow.Index].Value}' where studentId='{student.studentId}'";
            MySqlConnection mySqlConnection = new MySqlConnection(connectionStr);
            mySqlConnection.Open();
            MySqlCommand mySqlCommand = new MySqlCommand(editStr, mySqlConnection);
            
            mySqlCommand.ExecuteNonQuery();//对连接执行SQL语句并返回行数
            mySqlCommand.Dispose();//释放MySqlCommand资源
            mySqlConnection.Close();

        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void delete_Click(object sender, EventArgs e)
        {
            Student student = students[dataGridView1.CurrentRow.Index];
            string delStr = $"delete from student where studentId={dataGridView1["studentId", dataGridView1.CurrentRow.Index].Value}";
            MySqlConnection mySqlConnection = new MySqlConnection(connectionStr);
            mySqlConnection.Open();
            MySqlCommand mySqlCommand = new MySqlCommand(delStr,mySqlConnection);
           
            mySqlCommand.ExecuteNonQuery();
            mySqlCommand.Dispose();
            mySqlConnection.Close();

        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {

            new AddOrEdit().Show();//指定展示控件

        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void query_Click(object sender, EventArgs e)
        {
            string QueryStr = "select * from student";
            MySqlConnection mySqlConnection = new MySqlConnection(connectionStr);
            MySqlCommand mySqlCommand = new MySqlCommand(QueryStr, mySqlConnection);
            mySqlConnection.Open();
            MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
            
            students = new List<Student>();
            while (mySqlDataReader.Read())
            {
                students.Add(new Student(int.Parse(mySqlDataReader[0].ToString()), mySqlDataReader[1].ToString(), int.Parse(mySqlDataReader[2].ToString()), mySqlDataReader[3].ToString()));

            }
            mySqlCommand.Dispose();
            mySqlConnection.Close();
            dataGridView1.DataSource = students;
        }
    }
}
